package com.dao;

import javax.swing.table.DefaultTableModel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class RSMySQLData {
    private static Connection conn;
    private static PreparedStatement pstmt;
    private static ResultSet rs;
    public static final String USERNAME = "username";
    public static final String USERPHONE = "user_phone";
    public static final String USERPASWORD = "password";

    // 查询对比账号和密码是否正确，并返回相应的提示信息
    public static String authenticateCredentials(String inputUserPhone, String inputPassword) {
        conn = ServerLink.getConnection();  // 获取数据库连接
        if (conn != null) {
            try {
                String query = "SELECT * FROM users WHERE user_phone=?";  // 查询语句
                pstmt = conn.prepareStatement(query);  // 创建PreparedStatement对象
                pstmt.setString(1, inputUserPhone);  // 设置账号参数
                rs = pstmt.executeQuery();  // 执行查询
                if (rs.next()) {
                    String passwordFromDB = rs.getString("password");  // 从数据库中获取密码
                    if (passwordFromDB.equals(inputPassword)) {
                        return "登录成功";  // 密码正确，返回登录成功提示
                    } else {
                        return "密码错误";  // 密码错误，返回密码错误提示
                    }
                } else {
                    return "账号错误";  // 没有查询到id，返回账号错误提示
                }
            } catch (SQLException e) {
                e.printStackTrace();  // 打印异常信息
            } finally {
                ServerLink.close(rs);
                ServerLink.close(pstmt);
                ServerLink.close(conn);  // 关闭数据库连接
            }
        }
        return "数据库连接失败";  // 数据库连接失败或其他情况返回数据库连接失败提示
    }

    //修改、删除或增加数据

    //添加用户
    public static String addUser(String userName, String password, String userPhone) {
        conn = ServerLink.getConnection();  // 获取数据库连接
        if (conn != null) {
            try {
                String insertQuery = "INSERT INTO users (username, password, user_phone) VALUES (?, ?, ?)";  // 插入语句
                pstmt = conn.prepareStatement(insertQuery);  // 创建PreparedStatement对象
                pstmt.setString(1, userName);  // 设置用户名
                pstmt.setString(2, password);  // 设置密码
                pstmt.setString(3, userPhone);  // 设置用户电话
                int rowsAffected = pstmt.executeUpdate();  // 执行插入操作
                if (rowsAffected > 0) {
                    System.out.println("用户添加成功");
                    return "用户添加成功";
                }
            } catch (SQLException e) {
                e.printStackTrace();  // 打印异常信息
            } finally {
                ServerLink.close(pstmt);
                ServerLink.close(conn);  // 关闭数据库连接
            }
        }
        return "用户添加失败";
    }
    //删除用户
    public static String deleteUser(String userPhone, String password) {
        String response = authenticateCredentials(userPhone, password);
        if(response.equals("登录成功")) {
            conn = ServerLink.getConnection();  // 获取数据库连接
            if (conn != null) {
                try {
                    String deleteQuery = "DELETE FROM users WHERE username = ?";  // 删除语句
                    pstmt = conn.prepareStatement(deleteQuery);  // 创建PreparedStatement对象
                    pstmt.setString(1, userPhone);  // 设置要删除的用户ID
                    int rowsAffected = pstmt.executeUpdate();  // 执行删除操作
                    if (rowsAffected > 0) {
                        return "用户删除成功";
                    } else {
                        return "未找到匹配的用户";
                    }
                } catch (SQLException e) {
                    e.printStackTrace();  // 打印异常信息
                } finally {
                    ServerLink.close(pstmt);
                    ServerLink.close(conn);  // 关闭数据库连接
                }
            }
        }
        return response;
    }
    //修改用户信息
    public static String updateUser(int userId, String newUsername, String newPassword, String newUserPhone) {
        if(newUsername == null && newPassword == null && newUserPhone == null) {
            return null;
        }
        int i = 1;
        String updateQuery = "UPDATE users SET " +
                (newUsername != null ? "username = ?" : "") +/*判断newUsername是否为空*/
                (newUsername != null && newPassword != null ? ", " : "") +/*判断newUsername和newPassword都不为空时，在其中间加逗号*/
                (newPassword != null ? "password = ?" : "") +/*判断newPassword是否为空*/
                (newUserPhone != null && (newUsername != null || newPassword != null) ? ", " : "") +/*判断newUserPhone和其前面的(newUsername或newPassword)都不为空时，在其中间加逗号*/
                (newUserPhone != null ? "user_phone = ?" : "") +/*判断newUserPhone是否为空*/
                " WHERE id = ?";    // 更新语句
        //System.out.println(updateQuery);
        conn = ServerLink.getConnection();  // 获取数据库连接
        if (conn != null) {
            try {
                pstmt = conn.prepareStatement(updateQuery);  // 创建PreparedStatement对象
                if (newUsername != null) {
                    //System.out.println("name"+i);
                    pstmt.setString(i++, newUsername);  // 设置新的用户名
                }
                if (newPassword != null) {
                    //System.out.println("pass"+i);
                    pstmt.setString(i++, newPassword);  // 设置新的密码
                }
                if (newUserPhone != null) {
                    //System.out.println("phone"+i);
                    pstmt.setString(i++, newUserPhone);  // 设置新的用户电话
                }
                //System.out.println("id"+i);
                pstmt.setInt(i, userId);  // 设置要更新的用户ID
                int rowsAffected = pstmt.executeUpdate();  // 执行更新操作
                if (rowsAffected > 0) {
                   return "用户信息更新成功";
                }
            } catch (SQLException e) {
                e.printStackTrace();  // 打印异常信息
            } finally {
                ServerLink.close(pstmt);
                ServerLink.close(conn);  // 关闭数据库连接
            }
        }
        return "未找到匹配的用户或更新失败";
    }

    //更新积分
    public static void updateScore(int userID, int newUserScore) {
        conn = ServerLink.getConnection();  // 获取数据库连接
        if (conn != null) {
            try {
                String query = "SELECT * FROM users WHERE id=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setInt(1, userID);
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    int oldScore = rs.getInt("score");  // 从数据库中获取老积分
                    //判断新积分高于老积分
                    if (newUserScore > oldScore) {
                        String upScore = "UPDATE users SET score = ? WHERE id = ?";
                        pstmt = conn.prepareStatement(upScore);
                        pstmt.setInt(1, newUserScore);
                        pstmt.setInt(2, userID);
                        pstmt.executeUpdate();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                ServerLink.close(rs);
                ServerLink.close(pstmt);
                ServerLink.close(conn);
            }
        }
    }

    public static String searchData(int userID, String searchStr) {
        String query = "SELECT "+searchStr+" FROM users WHERE id=?";
        conn = ServerLink.getConnection();
        if (conn != null) {
            try {
                pstmt = conn.prepareStatement(query);
                pstmt.setInt(1, userID);
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    String str = rs.getString(searchStr);
                    return str;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                ServerLink.close(rs);
                ServerLink.close(pstmt);
                ServerLink.close(conn);
            }
        }
        return null;
    }

    //查询id号
    public static int searchID(String userPhone) {
        conn = ServerLink.getConnection();  // 获取数据库连接
        if (conn != null) {
            try {
                String query = "SELECT id FROM users WHERE user_phone=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, userPhone);
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    int id = rs.getInt("id");  // 从数据库中获取密码
                    return id;
                }
            } catch (SQLException e) {
                e.printStackTrace();  // 打印异常信息
            } finally {
                ServerLink.close(rs);
                ServerLink.close(pstmt);
                ServerLink.close(conn);  // 关闭数据库连接
            }
        }
        return -1;
    }

    //查询总排名
    public static DefaultTableModel selectGameScroe() {
        // 创建表格数据模型
        DefaultTableModel tableModel = new DefaultTableModel();
        tableModel.setColumnIdentifiers(new Object[]{"排名", "用户名", "积分"});
        conn = ServerLink.getConnection();  // 获取数据库连接
        if (conn != null) {
            try {
                // SQL查询语句，按score降序排列
                String sqlQuery = "SELECT * FROM users ORDER BY score DESC";
                // 创建PreparedStatement对象
                pstmt = conn.prepareStatement(sqlQuery);
                // 执行查询
                rs = pstmt.executeQuery();

                // 处理查询结果
                int rank = 1;
                while (rs.next()) {
                    String username = rs.getString("username");
                    int score = rs.getInt("score");
                    tableModel.addRow(new Object[]{rank, username, score});
                    //System.out.println(rank + " " + username + " " + score);
                    rank++;
                }
                return tableModel;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                ServerLink.close(rs);
                ServerLink.close(pstmt);
                ServerLink.close(conn);
            }
        }
        return tableModel;
    }

    //查询单排名
    public static int selectGameScroe(String userPhone) {
        conn = ServerLink.getConnection();  // 获取数据库连接
        if (conn != null) {
            try {
                // SQL查询语句，按score降序排列
                String sqlQuery = "SELECT * FROM users ORDER BY score DESC";
                // 创建PreparedStatement对象
                pstmt = conn.prepareStatement(sqlQuery);
                // 执行查询
                rs = pstmt.executeQuery();

                // 处理查询结果
                int rank = 1;
                while (rs.next()) {
                    String username = rs.getString("user_phone");
                    if(username.equals(userPhone)){
                        return rank;
                    }
                    rank++;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                ServerLink.close(rs);
                ServerLink.close(pstmt);
                ServerLink.close(conn);
            }
        }
        return -1;
    }
}